﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using KMF.Common;
using System.Text;

namespace KMF.DAL
{
    public partial class ProductDb
    {

        /// <summary>
        /// Lấy Top 4 sản phẩm theo danh mục, breadCrumb
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static List<ProductRateByCateTopListPagingDto> GetByBreadCrumbCateId(string categoryId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Client_Product_GetByBreadCrumbCategoryId");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                return dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);
            }
            finally
            {
                command.Connection.Close();
            }
        }

        /// <summary>
        /// dung cho truong hop ko co loc san pham
        /// </summary>
        /// <param name="page"></param>
        /// <param name="itemsPerPage"></param>
        /// <param name="totalPage"></param>
        /// <param name="categoryId"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public static List<ProductRateByCateTopListPagingDto> GetAllProductByCategoryId_Filter_BuyMost(int page, int itemsPerPage,
                                                                                    ref int totalPage, string categoryId, int filter)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByCategoryId_filter_BuyMost");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                command.Parameters.Add(dao.CreateParameter("@Filter", filter));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }
        public static List<ProductRateByCateTopListPagingDto> GetAllProductByCategoryId_Filter_Discount(int page, int itemsPerPage,
                                                                                            ref int totalPage, string categoryId, int filter)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByCategoryId_filter_discount");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                command.Parameters.Add(dao.CreateParameter("@Filter", filter));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());

                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }
        public static List<ProductRateByCateTopListPagingDto> GetAllProductByCategoryId_filter(int page, int itemsPerPage,
                                                                                            ref int totalPage, string categoryId, int filter)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByCategoryId_filter");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                command.Parameters.Add(dao.CreateParameter("@Filter", filter));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());

                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductListByPromotionPageDto> GetAllProductByPromotionPage(int page, int itemsPerPage, ref int totalPage, ref int totalRecord)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllPromotion");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                SqlParameter outTotalRecord = command.Parameters.Add("@TotalRecord", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                outTotalRecord.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductListByPromotionPageDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                totalRecord = int.Parse(outTotalRecord.Value.ToString() == "" ? "0" : outTotalRecord.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }
        public static List<ProductListByPromotionPageDto> GetAllProductByPromotionPage_buymost(int page, int itemsPerPage, ref int totalPage, ref int totalRecord)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllPromotion_buyMost");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                SqlParameter outTotalRecord = command.Parameters.Add("@TotalRecord", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                outTotalRecord.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductListByPromotionPageDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                totalRecord = int.Parse(outTotalRecord.Value.ToString() == "" ? "0" : outTotalRecord.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductListByPromotionPageDto> GetAllProductByPromotionPage_priceBest(int page, int itemsPerPage, ref int totalPage, ref int totalRecord)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllPromotion_priceBest");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                SqlParameter outTotalRecord = command.Parameters.Add("@TotalRecord", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                outTotalRecord.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductListByPromotionPageDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                totalRecord = int.Parse(outTotalRecord.Value.ToString() == "" ? "0" : outTotalRecord.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductListByPromotionPageDto> GetAllProductByPromotionPage_byCate(int page, int itemsPerPage, ref int totalPage, ref int totalRecord, string category = "")
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllPromotion_byCate");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@CategoryId", category));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                SqlParameter outTotalRecord = command.Parameters.Add("@TotalRecord", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                outTotalRecord.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductListByPromotionPageDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                totalRecord = int.Parse(outTotalRecord.Value.ToString() == "" ? "0" : outTotalRecord.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }
        /// <summary>
        /// Dung cho bo loc san pham cung cac tieu chi loc
        /// </summary>
        /// <param name="page"></param>
        /// <param name="itemsPerPage"></param>
        /// <param name="totalPage"></param>
        /// <param name="categoryId"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public static List<ProductRateByCateTopListPagingDto> SelectProductByFilter_banchay(int page, int itemsPerPage,
                                                                                            ref int totalPage, ref int totalRecord, string categoryId, int filter,
            string price = "", string rate = "", string provider = "", string color = "", string size = "", string attr = "")
        {
            var Tsql = new StringBuilder();
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByFilter_BuyMost");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                // filter
                decimal priceMin = 0;
                decimal priceMax = 0;
                Tsql.Append("SELECT DISTINCT ");
                Tsql.Append("ROW_NUMBER() OVER (ORDER BY ");
                switch (filter)
                {
                    case 0:
                        Tsql.Append("p.CreateDate DESC");
                        break;
                    case 1:
                        Tsql.Append("p.Price ASC");
                        break;
                    case 2:
                        Tsql.Append("p.Price DESC");
                        break;
                    case 3:
                        Tsql.Append("p.Name ASC");
                        break;
                    case 4:
                        Tsql.Append("p.Name DESC");
                        break;
                }
                Tsql.Append(") 'STT',");
                Tsql.Append("p.Id,");
                Tsql.Append("p.Barcode,");
                Tsql.Append("p.CategoryId,");
                Tsql.Append("p.Name,");
                Tsql.Append("p.Price,");
                Tsql.Append("p.Quantity,");

                Tsql.Append("UsePercentage = CASE WHEN dc.UsePercentage = 1 THEN dc.UsePercentage ");
                Tsql.Append("   WHEN d.UsePercentage = 1 THEN d.UsePercentage");
                Tsql.Append("   ELSE d.UsePercentage END,");

                Tsql.Append("DiscountPercentage = CASE WHEN dc.DiscountPercentage > 0 THEN dc.DiscountPercentage ");
                Tsql.Append("WHEN d.DiscountPercentage > 0 THEN d.DiscountPercentage ");
                Tsql.Append("ELSE 0 END,");

                Tsql.Append("DiscountAmount = CASE WHEN dc.DiscountAmount > 0 THEN dc.DiscountAmount ");
                Tsql.Append("WHEN d.DiscountAmount > 0 THEN d.DiscountAmount ");
                Tsql.Append("ELSE 0 END,");
                Tsql.Append("PromotionPrice = CASE WHEN dc.CatePromotionPrice > 0 THEN dc.CatePromotionPrice ");
                Tsql.Append("WHEN d.PromotionPrice > 0 THEN d.PromotionPrice ELSE 0 END, ");
                Tsql.Append("GiftId = CASE WHEN gc.GiftId > 0 THEN g.GiftId ");
                Tsql.Append("WHEN g.GiftId > 0 THEN g.GiftId ELSE 0 END, ");

                Tsql.Append("p.ProductStatus,");
                Tsql.Append("p.StockStatus,");
                Tsql.Append("p.Show,");
                Tsql.Append("p.CustomInfoFirst,");
                Tsql.Append("p.CustomInfoSecond,");
                Tsql.Append("p.CustomInfoThird,");
                Tsql.Append("p.CreateDate,");
                Tsql.Append("p.ImagePath,");
                Tsql.Append("pv.Name ProviderName,");
                Tsql.Append("pc.BreadCrumb,");
                Tsql.Append("p.TotalView,");
                Tsql.Append("p.AverageRate");
                Tsql.Append("   FROM");
                Tsql.Append("   Product p ");
                Tsql.Append("   LEFT JOIN Provider pv ON p.ProviderId = pv.Id");
                Tsql.Append("   INNER JOIN ProductCategory pc ON p.CategoryId = pc.Id");
                Tsql.Append("   INNER JOIN (select TOP 1000 ProductId from OrderDetail group by ProductId order by count(Quantity) DESC) o ON o.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_DiscountPromotionProduct d ON d.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_GiftPromotionProduct g ON g.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_DiscountPromotionCate dc ON dc.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_GiftPromotionCate gc ON gc.ProductId = p.Id ");
                // filter
                if (!string.IsNullOrEmpty(color))
                {
                    Tsql.Append("   LEFT JOIN ProductImage pim ON p.Id = pim.ProductId");
                }
                if (!string.IsNullOrEmpty(size))
                {
                    Tsql.Append("   LEFT JOIN ProductSize ps ON ps.ProductId = p.Id");
                }
                if (!string.IsNullOrEmpty(attr))
                {
                    Tsql.Append("   LEFT JOIN AttributeProduct at ON at.ProductId = p.Id");
                }

                Tsql.Append("   WHERE");
                Tsql.Append("   p.Show = 1");
                Tsql.Append("   AND pc.BreadCrumb LIKE '%/" + categoryId + "/%'");
                if (!string.IsNullOrEmpty(price))
                {
                    priceMin = Convert.ToDecimal(price.Split(';')[0]);
                    priceMax = Convert.ToDecimal(price.Split(';')[1]);
                    Tsql.Append("   AND p.Price >= " + priceMin);
                    Tsql.Append("   AND p.Price <= " + priceMax);
                }

                if (!string.IsNullOrEmpty(rate))
                {
                    Tsql.Append("   AND p.AverageRate >=  " + rate.Split(';')[0]);
                    Tsql.Append("   AND p.AverageRate <= " + rate.Split(';')[1]);
                }
                if (!string.IsNullOrEmpty(provider))
                {
                    Tsql.Append("   AND p.ProviderId IN (select * from [ConvertListIntoTable]('" + provider.Replace("'", "") + "',','))");
                }
                if (!string.IsNullOrEmpty(color))
                {
                    Tsql.Append("   AND pim.ColorId IN (select * from [ConvertListIntoTable]('" + color.Replace("'", "") + "',','))");
                }
                if (!string.IsNullOrEmpty(size))
                {
                    Tsql.Append("   AND ps.SizeId IN (select * from [ConvertListIntoTable]('" + size.Replace("'", "") + "',','))");
                }
                if (!string.IsNullOrEmpty(attr))
                {
                    Tsql.Append("   AND at.AttributeId IN (select * from [ConvertListIntoTable]('" + attr.Replace("'", "") + "',','))");
                }
                Tsql.Append("   GROUP BY ");
                Tsql.Append("p.Id ,");
                Tsql.Append("p.Barcode ,");
                Tsql.Append("p.CategoryId ,");
                Tsql.Append("p.NAME ,");
                Tsql.Append("p.Price ,");
                Tsql.Append("p.Quantity ,");
                Tsql.Append("dc.UsePercentage ,");
                Tsql.Append("d.UsePercentage,");
                Tsql.Append("dc.DiscountPercentage ,");
                Tsql.Append("d.DiscountPercentage,");
                Tsql.Append("dc.DiscountAmount ,");
                Tsql.Append("d.DiscountAmount,");
                Tsql.Append("d.PromotionPrice ,");
                Tsql.Append("dc.CatePromotionPrice ,");
                Tsql.Append("gc.GiftId,");
                Tsql.Append("g.GiftId,");
                Tsql.Append("p.ProductStatus ,");
                Tsql.Append("p.StockStatus ,");
                Tsql.Append("p.Show ,");
                Tsql.Append("p.CustomInfoFirst,");
                Tsql.Append("p.CustomInfoSecond ,");
                Tsql.Append("p.CustomInfoThird ,");
                Tsql.Append("p.CreateDate ,");
                Tsql.Append("p.ImagePath ,");
                Tsql.Append("pv.Name ,");
                Tsql.Append("pc.BreadCrumb ,");
                Tsql.Append("TotalView,");
                Tsql.Append("AverageRate");
                command.Parameters.Add(dao.CreateParameter("@Tsql", Tsql.ToString()));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                SqlParameter outTotalRecord = command.Parameters.Add("@TotalRecord", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                outTotalRecord.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                totalRecord = int.Parse(outTotalRecord.Value.ToString() == "" ? "0" : outTotalRecord.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }
        public static List<ProductRateByCateTopListPagingDto> SelectProductByFilter_khyenmai(int page, int itemsPerPage,
                                                                                            ref int totalPage, ref int totalRecord, string categoryId, int filter,
            string price = "", string rate = "", string provider = "", string color = "", string size = "", string attr = "")
        {
            var Tsql = new StringBuilder();
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByfilter_discount");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                decimal priceMin = 0;
                decimal priceMax = 0;
                Tsql.Append("SELECT DISTINCT ");
                Tsql.Append("ROW_NUMBER() OVER (ORDER BY ");
                switch (filter)
                {
                    case 0:
                        Tsql.Append("p.CreateDate DESC");
                        break;
                    case 1:
                        Tsql.Append("p.Price ASC");
                        break;
                    case 2:
                        Tsql.Append("p.Price DESC");
                        break;
                    case 3:
                        Tsql.Append("p.Name ASC");
                        break;
                    case 4:
                        Tsql.Append("p.Name DESC");
                        break;
                }
                Tsql.Append(") 'STT',");
                Tsql.Append("p.Id,");
                Tsql.Append("p.Barcode,");
                Tsql.Append("p.CategoryId,");
                Tsql.Append("p.Name,");
                Tsql.Append("p.Price,");
                Tsql.Append("p.Quantity,");

                Tsql.Append("UsePercentage = CASE WHEN dc.UsePercentage = 1 THEN dc.UsePercentage ");
                Tsql.Append("   WHEN d.UsePercentage = 1 THEN d.UsePercentage");
                Tsql.Append("   ELSE d.UsePercentage END,");

                Tsql.Append("DiscountPercentage = CASE WHEN dc.DiscountPercentage > 0 THEN dc.DiscountPercentage ");
                Tsql.Append("WHEN d.DiscountPercentage > 0 THEN d.DiscountPercentage ");
                Tsql.Append("ELSE 0 END,");

                Tsql.Append("DiscountAmount = CASE WHEN dc.DiscountAmount > 0 THEN dc.DiscountAmount ");
                Tsql.Append("WHEN d.DiscountAmount > 0 THEN d.DiscountAmount ");
                Tsql.Append("ELSE 0 END,");
                Tsql.Append("PromotionPrice = CASE WHEN dc.CatePromotionPrice > 0 THEN dc.CatePromotionPrice ");
                Tsql.Append("WHEN d.PromotionPrice > 0 THEN d.PromotionPrice ELSE 0 END, ");
                Tsql.Append("GiftId = CASE WHEN gc.GiftId > 0 THEN g.GiftId ");
                Tsql.Append("WHEN g.GiftId > 0 THEN g.GiftId ELSE 0 END, ");

                Tsql.Append("p.ProductStatus,");
                Tsql.Append("p.StockStatus,");
                Tsql.Append("p.Show,");
                Tsql.Append("p.CustomInfoFirst,");
                Tsql.Append("p.CustomInfoSecond,");
                Tsql.Append("p.CustomInfoThird,");
                Tsql.Append("p.CreateDate,");
                Tsql.Append("p.ImagePath,");
                Tsql.Append("pv.Name ProviderName,");
                Tsql.Append("pc.BreadCrumb,");
                Tsql.Append("p.TotalView,");
                Tsql.Append("p.AverageRate");
                Tsql.Append("   FROM");
                Tsql.Append("   Product p ");
                Tsql.Append("   LEFT JOIN Provider pv ON p.ProviderId = pv.Id");
                Tsql.Append("   INNER JOIN ProductCategory pc ON p.CategoryId = pc.Id");
                Tsql.Append("   INNER JOIN (select TOP 1000 ProductId from OrderDetail group by ProductId order by count(Quantity) DESC) o ON o.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_DiscountPromotionProduct d ON d.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_GiftPromotionProduct g ON g.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_DiscountPromotionCate dc ON dc.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_GiftPromotionCate gc ON gc.ProductId = p.Id ");
                // filter
                if (!string.IsNullOrEmpty(color))
                {
                    Tsql.Append("   LEFT JOIN ProductImage pim ON p.Id = pim.ProductId");
                }
                if (!string.IsNullOrEmpty(size))
                {
                    Tsql.Append("   LEFT JOIN ProductSize ps ON ps.ProductId = p.Id");
                }
                if (!string.IsNullOrEmpty(attr))
                {
                    Tsql.Append("   LEFT JOIN AttributeProduct at ON at.ProductId = p.Id");
                }

                Tsql.Append("   WHERE");
                Tsql.Append("   p.Show = 1");
                Tsql.Append("   AND pc.BreadCrumb LIKE '%/" + categoryId + "/%'");
                //Tsql.Append("   AND dc.UsePercentage = 1 AND d.UsePercentage = 1");
                //Tsql.Append("   AND dc.DiscountPercentage > 0 AND d.DiscountPercentage > 0");
                //Tsql.Append("   AND dc.DiscountAmount > 0 AND d.DiscountAmount > 0 ");
                Tsql.Append("   AND (dc.CatePromotionPrice > 0 OR d.PromotionPrice > 0)");
                //Tsql.Append("   AND gc.GiftId > 0 AND g.GiftId > 0");
                if (!string.IsNullOrEmpty(price))
                {
                    priceMin = Convert.ToDecimal(price.Split(';')[0]);
                    priceMax = Convert.ToDecimal(price.Split(';')[1]);
                    Tsql.Append("   AND p.Price >= " + priceMin);
                    Tsql.Append("   AND p.Price <= " + priceMax);
                }

                if (!string.IsNullOrEmpty(rate))
                {
                    Tsql.Append("   AND p.AverageRate >=  " + rate.Split(';')[0]);
                    Tsql.Append("   AND p.AverageRate <= " + rate.Split(';')[1]);
                }
                if (!string.IsNullOrEmpty(provider))
                {
                    Tsql.Append("   AND p.ProviderId IN (select * from [ConvertListIntoTable]('" + provider.Replace("'", "") + "',','))");
                }
                if (!string.IsNullOrEmpty(color))
                {
                    Tsql.Append("   AND pim.ColorId IN (select * from [ConvertListIntoTable]('" + color.Replace("'", "") + "',','))");
                }
                if (!string.IsNullOrEmpty(size))
                {
                    Tsql.Append("   AND ps.SizeId IN (select * from [ConvertListIntoTable]('" + size.Replace("'", "") + "',','))");
                }
                if (!string.IsNullOrEmpty(attr))
                {
                    Tsql.Append("   AND at.AttributeId IN (select * from [ConvertListIntoTable]('" + attr.Replace("'", "") + "',','))");
                }
                Tsql.Append("   GROUP BY ");
                Tsql.Append("p.Id ,");
                Tsql.Append("p.Barcode ,");
                Tsql.Append("p.CategoryId ,");
                Tsql.Append("p.NAME ,");
                Tsql.Append("p.Price ,");
                Tsql.Append("p.Quantity ,");
                Tsql.Append("dc.UsePercentage ,");
                Tsql.Append("d.UsePercentage,");
                Tsql.Append("dc.DiscountPercentage ,");
                Tsql.Append("d.DiscountPercentage,");
                Tsql.Append("dc.DiscountAmount ,");
                Tsql.Append("d.DiscountAmount,");
                Tsql.Append("d.PromotionPrice ,");
                Tsql.Append("dc.CatePromotionPrice ,");
                Tsql.Append("gc.GiftId,");
                Tsql.Append("g.GiftId,");
                Tsql.Append("p.ProductStatus ,");
                Tsql.Append("p.StockStatus ,");
                Tsql.Append("p.Show ,");
                Tsql.Append("p.CustomInfoFirst,");
                Tsql.Append("p.CustomInfoSecond ,");
                Tsql.Append("p.CustomInfoThird ,");
                Tsql.Append("p.CreateDate ,");
                Tsql.Append("p.ImagePath ,");
                Tsql.Append("pv.Name ,");
                Tsql.Append("pc.BreadCrumb ,");
                Tsql.Append("TotalView,");
                Tsql.Append("AverageRate");
                command.Parameters.Add(dao.CreateParameter("@Tsql", Tsql.ToString()));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                SqlParameter outTotalRecord = command.Parameters.Add("@TotalRecord", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                outTotalRecord.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                totalRecord = int.Parse(outTotalRecord.Value.ToString() == "" ? "0" : outTotalRecord.Value.ToString());

                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductRateByCateTopListPagingDto> SelectProductByFilter_moinhat(int page, int itemsPerPage,
                                                                                                    ref int totalPage, ref int totalRecord, string categoryId, int filter,
                    string price = "", string rate = "", string provider = "", string color = "", string size = "", string attr = "")
        {
            var Tsql = new StringBuilder();
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllBy_filter");
            decimal priceMin = 0;
            decimal priceMax = 0;
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                // filter
                Tsql.Append("SELECT DISTINCT ");
                Tsql.Append("ROW_NUMBER() OVER (ORDER BY ");
                switch (filter)
                {
                    case 0:
                        Tsql.Append("p.CreateDate DESC");
                        break;
                    case 1:
                        Tsql.Append("p.Price ASC");
                        break;
                    case 2:
                        Tsql.Append("p.Price DESC");
                        break;
                    case 3:
                        Tsql.Append("p.Name ASC");
                        break;
                    case 4:
                        Tsql.Append("p.Name DESC");
                        break;
                }
                Tsql.Append(") 'STT',");
                Tsql.Append("p.Id,");
                Tsql.Append("p.Barcode,");
                Tsql.Append("p.CategoryId,");
                Tsql.Append("p.Name,");
                Tsql.Append("p.Price,");
                Tsql.Append("p.Quantity,");

                Tsql.Append("UsePercentage = CASE WHEN dc.UsePercentage = 1 THEN dc.UsePercentage ");
                Tsql.Append("   WHEN d.UsePercentage = 1 THEN d.UsePercentage");
                Tsql.Append("   ELSE d.UsePercentage END,");

                Tsql.Append("DiscountPercentage = CASE WHEN dc.DiscountPercentage > 0 THEN dc.DiscountPercentage ");
                Tsql.Append("WHEN d.DiscountPercentage > 0 THEN d.DiscountPercentage ");
                Tsql.Append("ELSE 0 END,");

                Tsql.Append("DiscountAmount = CASE WHEN dc.DiscountAmount > 0 THEN dc.DiscountAmount ");
                Tsql.Append("WHEN d.DiscountAmount > 0 THEN d.DiscountAmount ");
                Tsql.Append("ELSE 0 END,");
                Tsql.Append("PromotionPrice = CASE WHEN dc.CatePromotionPrice > 0 THEN dc.CatePromotionPrice ");
                Tsql.Append("WHEN d.PromotionPrice > 0 THEN d.PromotionPrice ELSE 0 END, ");
                Tsql.Append("GiftId = CASE WHEN gc.GiftId > 0 THEN g.GiftId ");
                Tsql.Append("WHEN g.GiftId > 0 THEN g.GiftId ELSE 0 END, ");

                Tsql.Append("p.ProductStatus,");
                Tsql.Append("p.StockStatus,");
                Tsql.Append("p.Show,");
                Tsql.Append("p.CustomInfoFirst,");
                Tsql.Append("p.CustomInfoSecond,");
                Tsql.Append("p.CustomInfoThird,");
                Tsql.Append("p.CreateDate,");
                Tsql.Append("p.ImagePath,");
                Tsql.Append("pv.Name ProviderName,");
                Tsql.Append("pc.BreadCrumb,");
                Tsql.Append("p.TotalView,");
                Tsql.Append("p.AverageRate");
                Tsql.Append("   FROM");
                Tsql.Append("   Product p ");
                Tsql.Append("   LEFT JOIN Provider pv ON p.ProviderId = pv.Id");
                Tsql.Append("   INNER JOIN ProductCategory pc ON p.CategoryId = pc.Id");
                Tsql.Append("   LEFT JOIN V_DiscountPromotionProduct d ON d.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_GiftPromotionProduct g ON g.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_DiscountPromotionCate dc ON dc.ProductId = p.Id");
                Tsql.Append("   LEFT JOIN V_GiftPromotionCate gc ON gc.ProductId = p.Id ");
                // filter
                if (!string.IsNullOrEmpty(color))
                {
                    Tsql.Append("   LEFT JOIN ProductImage pim ON p.Id = pim.ProductId");
                }
                if (!string.IsNullOrEmpty(size))
                {
                    Tsql.Append("   LEFT JOIN ProductSize ps ON ps.ProductId = p.Id");
                }
                if (!string.IsNullOrEmpty(attr))
                {
                    Tsql.Append("   LEFT JOIN AttributeProduct at ON at.ProductId = p.Id");
                }

                Tsql.Append("   WHERE");
                Tsql.Append("   p.Show = 1");
                Tsql.Append("   AND pc.BreadCrumb LIKE '%/" + categoryId + "/%'");
                if (!string.IsNullOrEmpty(price))
                {
                    priceMin = Convert.ToDecimal(price.Split(';')[0]);
                    priceMax = Convert.ToDecimal(price.Split(';')[1]);
                    Tsql.Append("   AND p.Price >= " + priceMin);
                    Tsql.Append("   AND p.Price <= " + priceMax);
                }

                if (!string.IsNullOrEmpty(rate))
                {
                    Tsql.Append("   AND p.AverageRate >=  " + rate.Split(';')[0]);
                    Tsql.Append("   AND p.AverageRate <= " + rate.Split(';')[1]);
                }
                if (!string.IsNullOrEmpty(provider))
                {
                    Tsql.Append("   AND p.ProviderId IN (select * from [ConvertListIntoTable]('" + provider.Replace("'", "") + "',','))");
                }
                if (!string.IsNullOrEmpty(color))
                {
                    Tsql.Append("   AND pim.ColorId IN (select * from [ConvertListIntoTable]('" + color.Replace("'", "") + "',','))");
                }
                if (!string.IsNullOrEmpty(size))
                {
                    Tsql.Append("   AND ps.SizeId IN (select * from [ConvertListIntoTable]('" + size.Replace("'", "") + "',','))");
                }
                if (!string.IsNullOrEmpty(attr))
                {
                    Tsql.Append("   AND at.AttributeId IN (select * from [ConvertListIntoTable]('" + attr.Replace("'", "") + "',','))");
                }
                Tsql.Append("   GROUP BY ");
                Tsql.Append("p.Id ,");
                Tsql.Append("p.Barcode ,");
                Tsql.Append("p.CategoryId ,");
                Tsql.Append("p.NAME ,");
                Tsql.Append("p.Price ,");
                Tsql.Append("p.Quantity ,");
                Tsql.Append("dc.UsePercentage ,");
                Tsql.Append("d.UsePercentage,");
                Tsql.Append("dc.DiscountPercentage ,");
                Tsql.Append("d.DiscountPercentage,");
                Tsql.Append("dc.DiscountAmount ,");
                Tsql.Append("d.DiscountAmount,");
                Tsql.Append("d.PromotionPrice ,");
                Tsql.Append("dc.CatePromotionPrice ,");
                Tsql.Append("gc.GiftId,");
                Tsql.Append("g.GiftId,");
                Tsql.Append("p.ProductStatus ,");
                Tsql.Append("p.StockStatus ,");
                Tsql.Append("p.Show ,");
                Tsql.Append("p.CustomInfoFirst,");
                Tsql.Append("p.CustomInfoSecond ,");
                Tsql.Append("p.CustomInfoThird ,");
                Tsql.Append("p.CreateDate ,");
                Tsql.Append("p.ImagePath ,");
                Tsql.Append("pv.Name ,");
                Tsql.Append("pc.BreadCrumb ,");
                Tsql.Append("TotalView,");
                Tsql.Append("AverageRate");
                command.Parameters.Add(dao.CreateParameter("@Tsql", Tsql.ToString()));

                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                SqlParameter outTotalRecord = command.Parameters.Add("@TotalRecord", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                outTotalRecord.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                totalRecord = int.Parse(outTotalRecord.Value.ToString() == "" ? "0" : outTotalRecord.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductRateByCateTopListPagingDto> GetProductRateByCategoryId(int page, int itemsPerPage,
                                                                                            ref int totalPage, string categoryId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetRateByCategoryId");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());

                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductRateByCateTopListPagingDto> GetProductSPquantam_productDetail(int page, int itemsPerPage,
                                                                                            ref int totalPage, string productId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetSPquantam_detail");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@ProductId", productId));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());

                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductByCateTopListPagingDto> GetAllProductByCategoryIdBuyMost(int page, int itemsPerPage,
                                                                                    ref int totalPage, string categoryId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByCategoryIdByProductMost");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());

                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }
        public static List<ProductByCateTopListPagingDto> GetProductPromotionByCateAndTotalView(int page, int itemsPerPage,
                                                                                    ref int totalPage, string categoryId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_ProductCategory_GetPromotion");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);

                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());

                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductByCateTopListPagingDto> GetTopProductConcern(string categoryId, string productDate, string pId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByCateIdTop3");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                command.Parameters.Add(dao.CreateParameter("@ProductCreateDate", productDate));
                command.Parameters.Add(dao.CreateParameter("@productId", pId));
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductByCateTopListPagingDto> GetTopProductConcern_more(string categoryId, string pId)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByCateIdTop3_ByDate");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryId", categoryId));
                command.Parameters.Add(dao.CreateParameter("@productId", pId));
                var result = dao.GetDTOList<ProductByCateTopListPagingDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }


        public static List<ProductMenuHomeDto> GetByMenuHome(string category)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetTop4ByCategoryID_Menu_home");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryID", category));
                var result = dao.GetDTOList<ProductMenuHomeDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        /// <summary>
        /// o: order
        /// p: promotion
        /// v: view
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        public static List<ProductMenuHomeDto> GetTop3ProductByCategory(string category)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Home_Product_GetTop3ByCategoryID");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryID", category));
                var result = dao.GetDTOList<ProductMenuHomeDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        // PRICE FIL
        public static List<PriceFilterDto> GetPriceFilter(CategoryFilterModel modelFil)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Price_SelectByFilter");
            try
            {
                command.Parameters.Add(dao.CreateParameter("@CategoryId", modelFil.CategoryId));
                command.Parameters.Add(dao.CreateParameter("@PriceMin", modelFil.PriceMin));
                command.Parameters.Add(dao.CreateParameter("@PriceMax", modelFil.PriceMax));
                command.Parameters.Add(dao.CreateParameter("@RateMin", modelFil.RateMin));
                command.Parameters.Add(dao.CreateParameter("@RateMax", modelFil.RateMax));
                command.Parameters.Add(dao.CreateParameter("@Providers", modelFil.Providers));
                command.Parameters.Add(dao.CreateParameter("@Colors", modelFil.Colors));
                command.Parameters.Add(dao.CreateParameter("@Sizes", modelFil.Sizes));
                command.Parameters.Add(dao.CreateParameter("@Attrs", modelFil.Attrs));

                var result = dao.GetDTOList<PriceFilterDto>(ref command);
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static List<ProductRateByCateTopListPagingDto> GetAllProductByKeyword_home(int page, int itemsPerPage, ref int totalPage, string keyword)
        {
            var dao = SharedSqlDao;
            var command = dao.GetDbSprocCommand("WSPX_Product_GetAllByKeywordFullText");
            try
            {
                var keywords = BuildListKeywordV2(keyword);
                command.Parameters.Add(dao.CreateParameter("@Page", page));
                command.Parameters.Add(dao.CreateParameter("@ItemsPerPage", itemsPerPage));
                command.Parameters.Add(dao.CreateParameter("@Condition", keywords));
                SqlParameter outTotalpage = command.Parameters.Add("@TotalPage", SqlDbType.Int);
                outTotalpage.Direction = ParameterDirection.Output;
                var result = dao.GetDTOList<ProductRateByCateTopListPagingDto>(ref command);
                totalPage = int.Parse(outTotalpage.Value.ToString() == "" ? "0" : outTotalpage.Value.ToString());
                return result;
            }
            finally
            {
                command.Connection.Close();
            }
        }
    }
}
